<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>

<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
		"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
		"1234567890");
	Statement stmt;
	int ID, DegreeID;

	ID = Integer.valueOf(request.getParameter("ID"));
	DegreeID = Integer.valueOf(request.getParameter("degrees"));

	stmt = conn.createStatement();
	ResultSet rsDegree = stmt
			.executeQuery("SELECT * FROM degrees_offered WHERE degrees_id =" + String.valueOf(DegreeID));

	int lower_units_reqd = 0, upper_units_reqd=0, elective_units_reqd=0;
	String DegreeName = "";
	while (rsDegree.next()) {
		DegreeName = rsDegree.getString("name");
		lower_units_reqd = rsDegree.getInt("lower_units_reqd");
		upper_units_reqd = rsDegree.getInt("upper_units_reqd");
		elective_units_reqd = rsDegree.getInt("elective_units_reqd");
	}
	
	stmt = conn.createStatement();
	ResultSet rs = stmt
			.executeQuery("SELECT * FROM students WHERE student_id =" + String.valueOf(ID));

	String myFirstName = "", myMiddleName = "", myLastName = "";
	while (rs.next()) {
		myFirstName = rs.getString("first_name");
		myMiddleName = rs.getString("middle_name");
		myLastName = rs.getString("last_name");
	}

	
	rs.close();
	stmt.close();
	
	stmt = conn.createStatement();
	ResultSet rsUpper = stmt
			.executeQuery("SELECT * FROM (SELECT *, SUBSTRING(enroll.grade_letter,1,1) AS grade_letter_without_sign FROM students , enroll, classes WHERE students.student_id = enroll.student_id AND classes.class_id = enroll.section_id AND quarter = '2' and year = 2014 AND students.student_id ="
					+ String.valueOf(ID) + " AND classes.course_id in (select course_id from course_category where course_category.category_id in (select category_id from category where category.name = 'Upper Division'))) AS AAA WHERE grade_letter_without_sign IN ('A','B','C','P')");

	int countUpperUnits = 0;
	while (rsUpper.next()) {
		countUpperUnits += rsUpper.getInt("units");
	}
	rsUpper.close();
	stmt.close();
	
	stmt = conn.createStatement();
	ResultSet rsTechnical = stmt
			.executeQuery("SELECT * FROM (SELECT *, SUBSTRING(enroll.grade_letter,1,1) AS grade_letter_without_sign FROM students , enroll, classes WHERE students.student_id = enroll.student_id AND classes.class_id = enroll.section_id AND quarter = '2' and year = 2014 AND students.student_id ="
					+ String.valueOf(ID) + " AND classes.course_id in (select course_id from course_category where course_category.category_id in (select category_id from category where category.name = 'Technical Elective'))) AS AAA WHERE grade_letter_without_sign IN ('A','B','C','P')");

	int countTechnicalUnits = 0;
	while (rsTechnical.next()) {
		countTechnicalUnits += rsTechnical.getInt("units");
	}
	rsTechnical.close();
	stmt.close();

	stmt = conn.createStatement();
	ResultSet rsLower = stmt
			.executeQuery("SELECT * FROM (SELECT *, SUBSTRING(enroll.grade_letter,1,1) AS grade_letter_without_sign FROM students , enroll, classes WHERE students.student_id = enroll.student_id AND classes.class_id = enroll.section_id AND quarter = '2' and year = 2014 AND students.student_id ="
					+ String.valueOf(ID) + " AND classes.course_id in (select course_id from course_category where course_category.category_id in (select category_id from category where category.name = 'Lower Division'))) AS AAA WHERE grade_letter_without_sign IN ('A','B','C','P')");

	int countrsLowerUnits = 0;
	while (rsLower.next()) {
		countrsLowerUnits += rsLower.getInt("units");
	}
	rsLower.close();
	stmt.close();
	
%>
<!DOCTYPE html>
<html>
<head>
</head>
<body>
	<table border="1">
		<tr>
			<td valign="top" width="30%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>
				<p align="center">
					<font size="6">Display the classes currently taken by
						student</font>
				</p> <br> Full name: <%
 	out.print((myFirstName + " " + myMiddleName + " " + myLastName));
 %>
				<br>
				<br>
				<table border="1">
					<tr>
						<td>
							Degree Name: <%=DegreeName%>
						</td>
					</tr>
					<tr>
						<td>
							Degree needs Upper Units: <%=String.valueOf(upper_units_reqd)%>
						</td>
					</tr>
					<tr>
						<td>
							Degree needs Technical Elective Units: <%=String.valueOf(elective_units_reqd)%>
						</td>
					</tr>
					<tr>
						<td>
							Degree needs Lower Units: <%=String.valueOf(lower_units_reqd)%>
						</td>
					</tr>
					</tr>

				</table>
				<br>
				<table border="1">
					<tr>
						<td>
							Student earns Upper Units: <%=String.valueOf(countUpperUnits)%>
						</td>
					</tr>
					<tr>
						<td>
							Student earns Technical Elective Units: <%=String.valueOf(countTechnicalUnits)%>
						</td>
					</tr>
					<tr>
						<td>
							Student earns Lower Units: <%=String.valueOf(countrsLowerUnits)%>
						</td>
					</tr>

				</table>
				<br>
				<table border="1">
					<tr>
						<td>
							Student needs Upper Units: <%=String.valueOf(upper_units_reqd - countUpperUnits)%>
						</td>
					</tr>
					<tr>
						<td>
							Student needs Technical Elective Units: <%=String.valueOf(elective_units_reqd - countTechnicalUnits)%>
						</td>
					</tr>
					<tr>
						<td>
							Student needs Lower Units: <%=String.valueOf(lower_units_reqd - countrsLowerUnits)%>
						</td>
					</tr>

				</table>
			</td>
		</tr>
	</table>
</body>
<%
	// Close the Connection
	conn.close();
%>